package test.demo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

public class ExcelDataMerger {
    public static void main(String[] args) throws IOException {
        Scanner scanner = new Scanner(System.in);

        // 获取输入文件路径
        System.out.print("C:\\Users\\25468\\Desktop\\柴河水库111\\柴河水库洪水参数表.xlsx");
        String filePath = scanner.nextLine();

        System.out.print("C:\\Users\\25468\\Desktop\\柴河水库111\\out柴河水库洪水参数表.xlsx");
        String outputFilePath = scanner.nextLine();  // 提前定义变量，避免作用域问题

        FileInputStream file = new FileInputStream(new File(filePath));
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);

        Set<String> timeSet = new TreeSet<>(); // 用TreeSet自动排序
        Map<String, String> reservoirData = new HashMap<>();
        Map<String, String> slwData = new HashMap<>();

        for (Row row : sheet) {
            if (row.getRowNum() == 0) continue; // 跳过表头

            Cell timeCellReservoir = row.getCell(1); // B列（时间）
            Cell flowCellReservoir = row.getCell(2); // C列（入库流量）
            Cell timeCellSLW = row.getCell(5); // F列（时间）
            Cell flowCellSLW = row.getCell(6); // G列（入库流量）

            if (timeCellReservoir != null) {
                String timeReservoir = timeCellReservoir.toString();
                timeSet.add(timeReservoir);
                reservoirData.put(timeReservoir, flowCellReservoir != null ? flowCellReservoir.toString() : "");
            }

            if (timeCellSLW != null) {
                String timeSLW = timeCellSLW.toString();
                timeSet.add(timeSLW);
                slwData.put(timeSLW, flowCellSLW != null ? flowCellSLW.toString() : "");
            }
        }

        // 创建新表
        Workbook newWorkbook = new XSSFWorkbook();
        Sheet newSheet = newWorkbook.createSheet("Merged Data");
        Row headerRow = newSheet.createRow(0);
        headerRow.createCell(0).setCellValue("时间");
        headerRow.createCell(1).setCellValue("水库入库流量");
        headerRow.createCell(2).setCellValue("松辽委入库流量");

        int rowIndex = 1;
        for (String time : timeSet) {
            Row row = newSheet.createRow(rowIndex++);
            row.createCell(0).setCellValue(time);
            row.createCell(1).setCellValue(reservoirData.getOrDefault(time, ""));
            row.createCell(2).setCellValue(slwData.getOrDefault(time, ""));
        }

        FileOutputStream outputStream = new FileOutputStream(outputFilePath);
        newWorkbook.write(outputStream);
        outputStream.close();
        workbook.close();
        newWorkbook.close();
        scanner.close();

        System.out.println("数据合并完成，文件已生成：" + outputFilePath);
    }
}
